package connection;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.security.cert.Certificate;
import java.security.cert.CertificateEncodingException;
import java.security.cert.CertificateException;
import java.security.cert.CertificateFactory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import security.keyManagement.KeyStoreManager;
import utils.Base64Processing;
import utils.PropertiesUtils;
import connection.exception.CardErrorException;
import connection.exception.PropertiesUndefinedException;


public class DatabaseConnection {

	private Connection _dbConnection;

	private PreparedStatement _getUser;
	private PreparedStatement _getUserByUsername;
	private PreparedStatement _addUser;
	private PreparedStatement _getUserCompetency;
	private PreparedStatement _addUserCompetency;

	public DatabaseConnection(KeyStoreManager keyStoreManager, String propertiesAddress) throws PropertiesUndefinedException{
		Properties dbProperties = PropertiesUtils.getPropertiesFromFile(propertiesAddress);
		
		if(dbProperties == null)
			throw new PropertiesUndefinedException();

		Properties connectionProperties = new Properties();
		connectionProperties.put("user", dbProperties.getProperty("username"));
		connectionProperties.put("password", "P3TRdcXkS5");
		//TODO Alterar password para pass da keystore

		try{
			String targetDb = dbProperties.getProperty("dburl")+"/"+dbProperties.getProperty("dbname");

			_dbConnection = DriverManager.getConnection("jdbc:mysql://"+targetDb, connectionProperties);
		}catch (SQLException e) {
			e.printStackTrace();
		}

		configurePreparedStatements();
	}

	private void configurePreparedStatements(){
		String getUserQuery = "SELECT * FROM certcop.users WHERE FirstName = ? AND LastName = ?";
		String getUserByUsernameQuery = "SELECT * FROM certcop.users WHERE Username = ?";
		String addUserQuery = "INSERT INTO certcop.users (FirstName,LastName,Username,Password,Address,UserCert) VALUES ( ? , ? , ? , ? , ? , ? )";
		String getUserCompetencyQuery = "SELECT * FROM certcop.usercompetencies WHERE First_Name = ? AND Last_Name = ? AND Competency = ?";
		String addUserCompetencyQuery = "INSERT INTO certcop.usercompetencies (First_Name,Last_Name,Competency) VALUES ( ? , ? , ? )";

		try {
			_getUser = _dbConnection.prepareStatement(getUserQuery);
			_getUserByUsername = _dbConnection.prepareStatement(getUserByUsernameQuery);
			_addUser = _dbConnection.prepareStatement(addUserQuery);
			_getUserCompetency = _dbConnection.prepareStatement(getUserCompetencyQuery);
			_addUserCompetency = _dbConnection.prepareStatement(addUserCompetencyQuery);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public ResultSet getUser(String firstName, String lastName){
		try {
			_getUser.setString(1, firstName);
			_getUser.setString(2, lastName);
			
			ResultSet result = _getUser.executeQuery();
			result.last();
			
			if(result.getRow()==1){
				result.beforeFirst();
				_getUserCompetency.clearParameters();
				return result;
			}
			
			_getUser.clearParameters();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String getUserPasswordHash(String user){
		try{
			_getUserByUsername.setString(1, user);
			
			ResultSet result = _getUserByUsername.executeQuery();
			result.last();
			
			if(result.getRow()==1){
				return result.getString("Password");
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	public Certificate getUserCert(String fName, String lName) {
	    try{
            _getUser.setString(1, fName);
            _getUser.setString(2, lName);
            
            ResultSet result = _getUser.executeQuery();
            result.last();
            
            if(result.getRow()==1){
                ByteArrayInputStream bis = new ByteArrayInputStream(Base64Processing.decodeData(result.getString("UserCert")));
                CertificateFactory fact = CertificateFactory.getInstance("X509");
                return (Certificate) fact.generateCertificate(bis);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } catch (CertificateException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return null;
	}

	public int addUser(String firstName, String lastName, String username, String password, String address, String userCert){
		try {
			_addUser.setString(1, firstName);
			_addUser.setString(2, lastName);
			_addUser.setString(3, username);
			_addUser.setString(4, password);
			_addUser.setString(5, address);
			_addUser.setString(6, userCert);
			
			int status = _addUser.executeUpdate();
			
			_addUser.clearParameters();
			
			return status;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return -1;
	}

	public ResultSet getUserCompetency(String firstName, String lastName, String competency){
		try {
			_getUserCompetency.setString(1, firstName);
			_getUserCompetency.setString(2, lastName);
			_getUserCompetency.setString(3, competency);
			
			ResultSet result = _getUserCompetency.executeQuery();
			result.last();
			
			if(result.getRow()==1){
				result.beforeFirst();
				_getUserCompetency.clearParameters();
				return result;
			}
			
			_getUserCompetency.clearParameters();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

	public int addUserCompetency(String firstName, String lastName, String competency){
		try {
			_addUserCompetency.setString(1, firstName);
			_addUserCompetency.setString(2, lastName);
			_addUserCompetency.setString(3, competency);
			
			
			int status = _addUserCompetency.executeUpdate();
			
			_addUserCompetency.clearParameters();
			
			return status;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return 0;
	}

	public static void main(String[] args) throws IOException, SQLException, PropertiesUndefinedException, CertificateEncodingException, CardErrorException{

		DatabaseConnection conn=new DatabaseConnection(new KeyStoreManager("W2g4xmF8kc", true, true), "config/DBConf.properties");

//		conn._getUser.setString(1, "Jo�o");
//		conn._getUser.setString(2, "Lima");
//		ResultSet set = conn._getUser.executeQuery();
//
//		while(set.next()){
//			System.out.println(set.getString("Username"));
//		}
//
		// Adicionar Certificado na DB
//		String[] name = CitizenCardInterface.getFirstLastName();
//		String fname = name[0]; String lname = name[1];
//		String userCert = Base64Processing.encodeData(CitizenCardInterface.getCertificate().getEncoded());
//		conn.addUser(fname, lname, "csimoes", "csimoes", "Corroios", userCert);
//		//System.out.println(conn.getUser("a", "a"));

		conn._getUser.clearParameters();
	}

}
